package com.chinaway.mobile.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

import com.chinaway.base.BaseSpringJdbcDao;
import com.chinaway.mobile.dao.SimDao;
import com.chinaway.mobile.model.Account;
import com.chinaway.mobile.model.Group;
import com.chinaway.mobile.model.Sim;

public class SimDaoJdbc extends BaseSpringJdbcDao<Sim, Long> implements SimDao {
	
	private static String SQL_UPDATE = "update mg_sim set balance1=balance,balance=:balance,updatetime1 = updatetime,status = :status,updatetime = now(), remarks=:remarks where mobile = :mobile";
	
	@Override
	public void update(Sim sim) {
		getNamedParameterJdbcTemplate().update(SQL_UPDATE, new BeanPropertySqlParameterSource(sim));
	}
	
	private static String SQL_FINDBYMobile = "select * from  mg_sim where mobile = ?";
	@Override
	public Sim get(String mobile) {
		 return findUniqueBy(SQL_FINDBYMobile,mobile);
	}
	private static String SQL_RANDACOUNT = "SELECT mobile,password,provcode FROM mg_sim AS m1 JOIN "
	+ "(SELECT ROUND(RAND() * ((SELECT MAX(id) FROM mg_sim)-(SELECT MIN(id) FROM mg_sim)) + "
	+ "(SELECT MIN(id) FROM mg_sim)) AS id) AS m2 WHERE m1.id >= m2.id and m1.battime = ? ORDER BY m1.id LIMIT 10";
	@Override
	public List<Account> rand(Date date) {
		return (List<Account>)getSimpleJdbcTemplate().query(SQL_RANDACOUNT, accountMapper, date);
	}
	
	
	private static String SQL_FINDBY = "SELECT mobile,password,provcode  FROM mg_sim   where battime = ?";
	@Override
	public List<Account> findby(Date date) {
		return (List<Account>)getSimpleJdbcTemplate().query(SQL_FINDBY, accountMapper, date);
	}
	
	ParameterizedRowMapper<Account> accountMapper = new ParameterizedRowMapper<Account>()
	 {
		 public Account mapRow(ResultSet rs, int rowNum) throws SQLException
		 {
			  String mobile = rs.getString(1);
			  String password = rs.getString(2);
			  String provcode = rs.getString(3);
			 return  new Account(mobile,password,provcode);
		 }
	 };

	private static String SQL_BATCHDATE = "select DISTINCT(m1.battime) ,m2.linkman,m2.email " +
			"from mg_sim as m1 LEFT JOIN mg_customer as m2 on m1.clientid = m2.customerid where m1.battime !='0000-00-00'" ;
	@Override
	public List<Group> getbatchDate() {
		return getSimpleJdbcTemplate().query(SQL_BATCHDATE,new ParameterizedRowMapper<Group>(){
			public Group mapRow(ResultSet rs, int rowNum) throws SQLException {
				Date date = null;
				Timestamp ts = rs.getTimestamp(1);
				if(ts!=null){
					date = new Date(rs.getTimestamp(1).getTime());
				}else{
					 return null;
				}
				String name = rs.getString(2);
				String email = rs.getString(3);
				return new Group(date,name,email);
			}});
		
	}
}
